#from pysqlcipher3 import dbapi2 as sqlcipher
import sqlite3
import re
import os

def play(_table):
    bars_name_list = [descriptions[0] for descriptions in conn.execute("select * from %s" %_table).description]
    bars_name_var = ','.join([descriptions[0] for descriptions in conn.execute("select * from %s" %_table).description])
    cursor = c.execute("SELECT %s from %s" %(bars_name_var,_table))
    for row in cursor:
        print(dict(zip(bars_name_list, row)))

def insert_info(_table, _values_tuple):
    bars_name_var = ','.join([descriptions[0] for descriptions in conn.execute("select * from %s" %_table).description])
    c.execute("INSERT INTO %s (%s) VALUES %s" %(_table,bars_name_var,str(_values_tuple)))

def delete(_table,_where_str):
    c.execute("DELETE from %s where %s;" %(_table,_where_str))

def update(_table,_where_str,_set_str):
    c.execute("UPDATE %s set %s where %s;" %(_table,_set_str,_where_str))

def regexp(expr, item):
    rec = re.compile(expr)
    return rec.search(item) is not None

def select(_table,expre):
    bars_name_list = [descriptions[0] for descriptions in conn.execute("select * from %s" %_table).description]
    bars_name_var = ','.join([descriptions[0] for descriptions in conn.execute("select * from %s" %_table).description])
    rows=[]
    for row in c.execute('SELECT %s FROM %s WHERE %s' %(bars_name_var,_table,expre)):
        rows.append(dict(zip(bars_name_list, row)))
    return rows
    
def select_re(_table,_bar_name_str,expre):
    conn.create_function("REGEXP", 2, regexp)
    c.execute('SELECT %s FROM %s WHERE %s REGEXP ?' %(_bar_name_str,_table,_bar_name_str),[expre])
    return c.fetchall()



try:os.remove('1.db')
except:pass

conn = sqlite3.connect('1.db')
c = conn.cursor()
this_table = 'A'
try:c.execute('''

        CREATE TABLE %s (
            ID INT PRIMARY  KEY     NOT NULL,
            NAME            TEXT    NOT NULL,
            AGE             INT     NOT NULL,
            ADDRESS         CHAR(50),
            SALARY          REAL

        );''' %this_table)
except:pass
try:insert_info(this_table, (1, 'Paul', 32, 'California', 20000.00 ))
except:pass
try:insert_info(this_table, (2, 'Allen', 25, 'Texas', 15000.00 ))
except:pass
try:insert_info(this_table, (3, 'Teddy', 23, 'Norway', 20000.00 ))
except:pass
try:insert_info(this_table, (4, 'Mark', 25, 'Rich-Mond ', 65000.00 ))
except:pass

play(this_table)

print(select_re(this_table,'name','A'))

print(select(this_table,'name="Allen" or id=3'))

for each in select_re(this_table,'name','A'):
    print(select(this_table,'name="%s"' %each[0]))

conn.commit()
